﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.SqlClient;

namespace DExcelServiceApp
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“DExcelService”。
    public class DExcelService : IDExcelService
    {
        public bool SaveValueTable(Guid bfid, Guid orgid, string docType, string valueTable)
        {
            SqlConnection connection = new SqlConnection("server=.;database=DExcelDB;uid=sa;pwd=pass@word1");
            try
            {
                connection.Open();

                SqlCommand deleteCommand = connection.CreateCommand();
                deleteCommand.CommandText = "delete from valuetable where bfid=@bfid and orgid=@orgid and doctype=@doctype";
                SqlParameter dpbfid = new SqlParameter("@bfid", bfid);
                SqlParameter dporgid = new SqlParameter("@orgid", orgid);
                SqlParameter dpdoctype = new SqlParameter("@doctype", docType);
                deleteCommand.Parameters.Add(dpbfid);
                deleteCommand.Parameters.Add(dporgid);
                deleteCommand.Parameters.Add(dpdoctype);
                deleteCommand.ExecuteNonQuery();


                string sql = "insert into valuetable (id,bfid,orgid,doctype,path,valuestring) values (@id,@bfid,@orgid,@doctype,@path,@valuestring)";
                
                string[] segements = valueTable.Split(new char[] { ',' });
                int count = segements.Length / 2;
                for (int iCount = 0; iCount < count; iCount++)
                {
                    string path = segements[iCount * 2];
                    string value = segements[iCount * 2 + 1];
                    if (!string.IsNullOrEmpty(value))
                    {
                        SqlCommand command = new SqlCommand(sql, connection);
                        SqlParameter pid = new SqlParameter("@id", Guid.NewGuid());
                        SqlParameter pbfid = new SqlParameter("@bfid", bfid);
                        SqlParameter porgid = new SqlParameter("@orgid", orgid);
                        SqlParameter pdoctype = new SqlParameter("@doctype", docType);
                        SqlParameter ppath = new SqlParameter("@path", path);
                        SqlParameter pvaluestring = new SqlParameter("@valuestring", value);
                        command.Parameters.Add(pid);
                        command.Parameters.Add(pbfid);
                        command.Parameters.Add(porgid);
                        command.Parameters.Add(pdoctype);
                        command.Parameters.Add(ppath);
                        command.Parameters.Add(pvaluestring);
                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
            }
            finally
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return false;
        }


        public string GetData(Guid bfid, Guid orgid, string docType, string path)
        {
            string result = string.Empty;

            SqlConnection connection = new SqlConnection("server=.;database=DExcelDB;uid=sa;pwd=pass@word1");
            try
            {
                string sql = "select * from valuetable where bfid=@bfid and orgid=@orgid and doctype=@doctype and path=@path";
                connection.Open();

                SqlCommand command = new SqlCommand(sql, connection);
                SqlParameter pid = new SqlParameter("@id", Guid.NewGuid());
                SqlParameter pbfid = new SqlParameter("@bfid", bfid);
                SqlParameter porgid = new SqlParameter("@orgid", orgid);
                SqlParameter pdoctype = new SqlParameter("@doctype", docType);
                SqlParameter ppath = new SqlParameter("@path", path);
                command.Parameters.Add(pid);
                command.Parameters.Add(pbfid);
                command.Parameters.Add(porgid);
                command.Parameters.Add(pdoctype);
                command.Parameters.Add(ppath);
                SqlDataReader dataReader = command.ExecuteReader();
                if (dataReader.Read())
                {
                    result = Convert.ToString(dataReader["valuestring"]);
                }
            }
            catch
            {
            }
            finally
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }

        public string GetDatas(Guid bfid, Guid orgid, string docType)
        {
            StringBuilder result = new StringBuilder();

            bool isFirst = true;
            SqlConnection connection = new SqlConnection("server=.;database=DExcelDB;uid=sa;pwd=pass@word1");
            try
            {
                string sql = "select * from valuetable where bfid=@bfid and orgid=@orgid and doctype=@doctype";
                connection.Open();

                SqlCommand command = new SqlCommand(sql, connection);
                SqlParameter pid = new SqlParameter("@id", Guid.NewGuid());
                SqlParameter pbfid = new SqlParameter("@bfid", bfid);
                SqlParameter porgid = new SqlParameter("@orgid", orgid);
                SqlParameter pdoctype = new SqlParameter("@doctype", docType);
                command.Parameters.Add(pid);
                command.Parameters.Add(pbfid);
                command.Parameters.Add(porgid);
                command.Parameters.Add(pdoctype);
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    string vPath = Convert.ToString(dataReader["path"]);
                    string vString = Convert.ToString(dataReader["valuestring"]);

                    if (isFirst)
                    {
                        isFirst = false;
                        result.AppendFormat("{0},{1}", vPath, vString);
                    }
                    else
                    {
                        result.AppendFormat(",{0},{1}", vPath, vString);
                    }
                }
            }
            catch
            {
            }
            finally
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result.ToString();
        }
    }
}
